package com.system.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.system.demo.entity.database.Paper;
import com.system.demo.entity.mailPage.PaperVO;
import com.system.demo.entity.mailPage.PaperWorkVO;
import com.system.demo.mapper.dynamicSqlProvider.PaperSqlProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PaperSubmitMapper extends BaseMapper<Paper> {

    @Delete("DELETE FROM paper WHERE id = #{id} AND uid = #{uid}")
    void deleteById(@Param("id") Integer id, @Param("uid") String uid);

    @Select("SELECT * FROM paper WHERE id = #{id}")
    Paper findByApplicationId(@Param("id") Integer id);

    @Update({
            "UPDATE paper ",
            "SET final_level = #{newLevel}, permission_time = NOW() ",
            "WHERE id IN ${idListStr}"
    })
    int batchUpdateFinalLevel(@Param("idListStr") String idListStr, @Param("newLevel") String newLevel);


    @Update({
            "UPDATE paper ",
            "SET status = #{newStatus}",
            "WHERE id IN ${idListStr}"
    })
    int batchUpdateStatus(@Param("idListStr") String idListStr, @Param("newStatus") Integer newStatus);

    @SelectProvider(type = PaperSqlProvider.class, method = "getCombinedApplicationDataSql")
    List<Paper> getCombinedApplicationData(@Param("status") Integer status,
                                           @Param("type") Integer type,
                                           @Param("finalLevel") Integer finalLevel,
                                           @Param("stuName") String stuName,
                                           @Param("startTime") String startTime,
                                           @Param("endTime") String endTime,
                                           @Param("stuClass") String stuClass);


    @Select({
            "SELECT student_class",
            "FROM student",
            "WHERE uid = #{uid}"
    })
    String getPaperWithStudentClass(@Param("uid") String uid);

    @Update({
            "UPDATE paper ",
            "SET topic = null, final_level = null, permission_time = null",
            "WHERE id IN ${idListStr}"
    })
    int batchUpdateCertificate(@Param("idListStr") String idListStr);


    @Select("<script>" +
            "SELECT " +
            "  COALESCE(p.count, 0) AS count, " +
            "  COALESCE(p.paper, 0) AS paper, " +
            "  COALESCE(p.patent, 0) AS patent, " +
            "  COALESCE(p.software, 0) AS software " +
            "FROM (" +
            "  SELECT " +
            "    COUNT(*) AS count, " +
            "    SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END) AS paper, " +
            "    SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS patent, " +
            "    SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS software " +
            "  FROM " +
            "    paper " +
            "  WHERE " +
            "    uid = #{uid} AND `status` = 5" +
            ") p " +
            "RIGHT JOIN (SELECT 1) AS dummy ON TRUE" +
            "</script>")
    PaperVO selectTypeCounts(@Param("uid") String uid);

    @Select("<script>" +
            "SELECT " +
            "  COALESCE(p.count, 0) AS count, " +
            "  COALESCE(p.paper, 0) AS paper, " +
            "  COALESCE(p.patent, 0) AS patent, " +
            "  COALESCE(p.software, 0) AS software " +
            "FROM (" +
            "  SELECT " +
            "    COUNT(*) AS count, " +
            "    SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END) AS paper, " +
            "    SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS patent, " +
            "    SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS software " +
            "  FROM paper " +
            "  WHERE status = 5" +
            ") p " +
            "RIGHT JOIN (SELECT 1) AS dummy ON TRUE" +
            "</script>")
    PaperVO selectTypeCountsByAdmin();

    @Select("<script>" +
            "SELECT " +
            "  COALESCE(SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END), 0) AS pendingReview, " +
            "  COALESCE(SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END), 0) AS toBeRecognized " +
            "FROM paper" +
            "</script>")
    PaperWorkVO selectStatusCounts();

}
